package com.ljd.repository.impl;

import com.ljd.entity.Book;
import com.ljd.entity.BookCase;
import com.ljd.entity.Borrow;
import com.ljd.entity.Reader;
import com.ljd.repository.BorrowRepository;
import com.ljd.utils.JDBCTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BorrowRepositoryImpl implements BorrowRepository {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    @Override
    public void insert(Integer bookid, Integer readerid, String borrowtime, String returntime, Integer adminid) {
        String sql = "insert into borrow(bookid,readerid,borrowtime,returntime,state) values(?,?,?,?,0)";
        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, bookid);
            ps.setInt(2, readerid);
            ps.setString(3, borrowtime);
            ps.setString(4, returntime);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(conn, ps, rs);
        }
    }

    @Override
    public List<Borrow> findAllByReaderId(Integer readerid, Integer index, Integer limit) {
        String sql = "select br.id,b.name,author,bc.name,publish,borrowtime,returntime,r.name,tel,cardid,br.state" +
                " from borrow br, book b, reader r, bookcase bc" +
                " where readerid=? and br.bookid=b.id and br.readerid=r.id and b.bookcaseid=bc.id" +
                " limit ?,?";
        List<Borrow> list = new ArrayList<Borrow>();
        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, readerid);
            ps.setInt(2, index);
            ps.setInt(3, limit);
            rs = ps.executeQuery();
            list =getBorrowsByResultSet(rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(conn, ps, rs);
        }
        return list;
    }

    private List<Borrow> getBorrowsByResultSet(ResultSet rs1){
        List<Borrow> list = new ArrayList<Borrow>();
        try {
            while (rs1.next()) {
                //获取数据
                list.add(new Borrow(rs.getInt(1),
                        new Book(rs.getString(2),
                                rs.getString(3),
                                rs.getString(5),
                                new BookCase(rs.getString(4))),
                        new Reader(rs.getString(8),
                                rs.getString(9),
                                rs.getString(10)),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(11)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally{
            try {
                rs1.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<Borrow> findAllByState(Integer state, Integer index, Integer limit) {
        String sql = "select br.id,b.name,author,bc.name,publish,borrowtime,returntime,r.name,tel,cardid,br.state " +
                "from borrow br, book b, reader r, bookcase bc " +
                "where state=? and br.bookid=b.id and br.readerid=r.id and b.bookcaseid=bc.id " +
                "limit ?,?";
        List<Borrow> list = new ArrayList<Borrow>();
        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, state);
            ps.setInt(2, index);
            ps.setInt(3, limit);
            rs = ps.executeQuery();
            list = getBorrowsByResultSet(rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(conn, ps, rs);
        }
        return list;
    }

    @Override
    public Integer getCountByReaderid(Integer readerid) {
        String sql = "select count(*) from borrow br where readerid=?";
        Integer count = null;
        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, readerid);
            rs = ps.executeQuery();
            while (rs.next()) {
                count =rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(conn, ps, rs);
        }
        return count;
    }

    @Override
    public Integer getCountByState(Integer state) {
        String sql = "select count(*) from borrow br where state=?";
        Integer count = null;
        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, state);
            rs = ps.executeQuery();
            while (rs.next()) {
                count =rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(conn, ps, rs);
        }
        return count;
    }

    @Override
    public void handleBorrow(Integer borrowid, Integer state, Integer adminid) {
        String sql = "update borrow set adminid=?, state=? where id=?";
        Integer count = null;
        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, adminid);
            ps.setInt(2, state);
            ps.setInt(3, borrowid);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(conn, ps, rs);
        }
    }
}
